package com.gzx.plugin.tjzy.modular.work.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.gzx.plugin.tjzy.modular.work.entity.GzxMediatorWork;
import com.gzx.plugin.tjzy.modular.work.entity.vo.GzxMediatorWorkVo;
import com.gzx.plugin.tjzy.modular.work.entity.vo.GzxPreRepaymentVo;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.common.mapper.BaseMapperPlus;

import java.util.List;

/**
 * 调解员作业记录Mapper接口
 *
 * @author LionLi
 * @date 2024-09-18
 */
public interface GzxMediatorWorkMapper extends BaseMapperPlus<GzxMediatorWork, GzxMediatorWorkVo> {
    /**
     * 批量新增作业记录
     *
     * @param valSql
     */
    @Insert("<script>" +
            "INSERT INTO gzx_mediator_work (case_id, debtor_card_id, project_code, liaison_phone, work_type, work_result, liaison_name, call_recipient, phone_source,affiliated_company,tenant_id,create_user,create_time) values ${valSql}" +
            "</script>")
    void insertBatchByValSql(String valSql);

    @Select("WITH dateParam AS (SELECT #{selectDate} AS today,DATE(DATE_ADD(#{selectDate}, INTERVAL 1 DAY)) AS tomorrow) " +
            "SELECT " +
            "#{affiliatedCompany} affiliatedCompany,mw.project_code,true hasChildren," +
            "COUNT(CASE WHEN mw.promise_repayment_day = dp.today AND mw.promise_repayment_amount > 0 THEN 1 END) AS todayPreCount," +
            "SUM(CASE WHEN mw.promise_repayment_day = dp.today THEN mw.promise_repayment_amount ELSE 0 END) AS todayPreAmount," +
            "COUNT(CASE WHEN mw.promise_repayment_day = dp.tomorrow AND mw.promise_repayment_amount > 0 THEN 1 END) AS tomorrowPreCount," +
            "SUM(CASE WHEN mw.promise_repayment_day = dp.tomorrow THEN mw.promise_repayment_amount ELSE 0 END) AS tomorrowPreAmount " +
            "FROM gzx_mediator_work mw CROSS JOIN dateParam dp " +
            "WHERE mw.affiliated_company = #{affiliatedCompany} AND mw.promise_repayment_day IN (dp.today, dp.tomorrow)" +
            "GROUP BY mw.project_code " +
            "HAVING SUM(CASE WHEN mw.promise_repayment_day IN (dp.today, dp.tomorrow) THEN mw.promise_repayment_amount ELSE 0 END) > 0")
//    @Results({@Result(property = "children", column = "{projectCode=project_code,affiliatedCompany=affiliatedCompany}", javaType = List.class,
//            many = @Many(select = "getUserCountAndAmount"))})
    Page<GzxPreRepaymentVo> getProjectCountAndAmount(@Param("page") Page<GzxPreRepaymentVo> page, @Param("affiliatedCompany") Long affiliatedCompany, @Param("selectDate") String selectDate);

    @Select("WITH dateParam AS (SELECT #{selectDate} AS today,DATE(DATE_ADD(#{selectDate}, INTERVAL 1 DAY)) AS tomorrow) " +
            "SELECT " +
            "#{affiliatedCompany} affiliatedCompany,mw.project_code,true hasChildren," +
            "COUNT(CASE WHEN mw.promise_repayment_day = dp.today AND mw.promise_repayment_amount > 0 THEN 1 END) AS todayPreCount," +
            "SUM(CASE WHEN mw.promise_repayment_day = dp.today THEN mw.promise_repayment_amount ELSE 0 END) AS todayPreAmount," +
            "COUNT(CASE WHEN mw.promise_repayment_day = dp.tomorrow AND mw.promise_repayment_amount > 0 THEN 1 END) AS tomorrowPreCount," +
            "SUM(CASE WHEN mw.promise_repayment_day = dp.tomorrow THEN mw.promise_repayment_amount ELSE 0 END) AS tomorrowPreAmount " +
            "FROM gzx_mediator_work mw CROSS JOIN dateParam dp " +
            "WHERE mw.affiliated_company = #{affiliatedCompany} AND mw.promise_repayment_day IN (dp.today, dp.tomorrow)" +
            "GROUP BY mw.project_code " +
            "HAVING SUM(CASE WHEN mw.promise_repayment_day IN (dp.today, dp.tomorrow) THEN mw.promise_repayment_amount ELSE 0 END) > 0")
//    @Results({@Result(property = "children", column = "{projectCode=project_code,affiliatedCompany=affiliatedCompany}", javaType = List.class,
//            many = @Many(select = "getUserCountAndAmount"))})
    List<GzxPreRepaymentVo> getProjectCountAndAmountExport(@Param("affiliatedCompany") Long affiliatedCompany, @Param("selectDate") String selectDate);

    @Select("WITH dateParam AS (SELECT #{selectDate} AS today,DATE(DATE_ADD(#{selectDate}, INTERVAL 1 DAY)) AS tomorrow) " +
            "SELECT " +
            "mw.create_by," +
            "mw.create_name projectName," +
            "group_concat(mw.id) ids," +
            //"true hasChildren," +
            "COUNT(CASE WHEN mw.promise_repayment_day = dp.today AND mw.promise_repayment_amount > 0 THEN 1 END) AS todayPreCount," +
            "SUM(CASE WHEN mw.promise_repayment_day = dp.today THEN mw.promise_repayment_amount ELSE 0 END) AS todayPreAmount," +
            "COUNT(CASE WHEN mw.promise_repayment_day = dp.tomorrow AND mw.promise_repayment_amount > 0 THEN 1 END) AS tomorrowPreCount," +
            "SUM(CASE WHEN mw.promise_repayment_day = dp.tomorrow THEN mw.promise_repayment_amount ELSE 0 END) AS tomorrowPreAmount " +
            "FROM gzx_mediator_work mw " +
            "CROSS JOIN " +
            "dateParam dp " +
            "WHERE mw.affiliated_company = #{affiliatedCompany} AND mw.promise_repayment_day IN (dp.today, dp.tomorrow) and mw.project_code = #{projectCode} " +
            "GROUP BY mw.create_by, mw.create_name " +
            "HAVING SUM(CASE WHEN mw.promise_repayment_day IN (dp.today, dp.tomorrow) THEN mw.promise_repayment_amount ELSE 0 END) > 0")
    List<GzxPreRepaymentVo> getUserCountAndAmount(@Param("affiliatedCompany") Long affiliatedCompany, @Param("projectCode") String projectCode, @Param("selectDate") String selectDate);

    //获取当日的总数量
    @Select("SELECT count(*) FROM gzx_mediator_work mw INNER JOIN gzx_case gc ON mw.case_id = gc.id  " +
            "WHERE mw.create_time BETWEEN CONCAT(DATE_FORMAT(#{currentTime}, '%Y-%m-%d'),' 00:00:00') and CONCAT(DATE_FORMAT(#{currentTime}, '%Y-%m-%d'),' 23:59:59') "
            + "AND mw.liaison_name IS NOT NULL "
            + "AND gc.company_code = 'WXJK'"
            + "AND mw.project_code = 'WXJK'"
            )
    int getDayTotal(@Param("currentTime") String currentTime);

    //分批获取当日的数据
    @Select("SELECT mw.liaison_name as liaisonName , mw.create_time as createTime,mw.work_type as workType,mw.call_recipient as callRecipient,mw.liaison_phone as liaisonPhone ,mw.work_result as workResult,  gc.partner_business_id as partnerBusinessId,mw.result_code resultCode,mw.remark,mw.promise_repayment_day promiseRepaymentDay,mw.promise_repayment_amount promiseRepaymentAmount " +
            "FROM gzx_mediator_work mw INNER JOIN gzx_case gc ON mw.case_id = gc.id "+
            "WHERE mw.create_time BETWEEN CONCAT(DATE_FORMAT(#{currentTime}, '%Y-%m-%d'),' 00:00:00') and CONCAT(DATE_FORMAT(#{currentTime}, '%Y-%m-%d'),' 23:59:59') "+
            "AND mw.liaison_name IS NOT NULL "+
            "AND gc.company_code = 'WXJK' AND mw.project_code = 'WXJK' "+
            "LIMIT #{offset}, #{limit}")
    List<GzxMediatorWorkVo> selectDayMediaWork(@Param("offset") int offset, @Param("limit") int limit,@Param("currentTime") String currentTime);

    
	// 获取总数量：历史
	@Select("SELECT count(*) FROM gzx_mediator_work mw INNER JOIN gzx_case gc ON mw.case_id = gc.id   "
			+ "WHERE mw.create_time BETWEEN #{start} and #{end} "
			+ "AND mw.liaison_name IS NOT NULL "
			+ "AND gc.company_code = 'WXJK' "
			+ "AND mw.project_code = 'WXJK'")
	int getHistoryTotal(@Param("start") String start, @Param("end") String end);

	// 分批获取数据：历史
	@Select("SELECT mw.liaison_name as liaisonName , mw.create_time as createTime,mw.work_type as workType,mw.call_recipient as callRecipient,mw.liaison_phone as liaisonPhone ,mw.work_result as workResult,  gc.partner_business_id as partnerBusinessId,mw.result_code resultCode,mw.remark,mw.promise_repayment_day promiseRepaymentDay,mw.promise_repayment_amount promiseRepaymentAmount  "
			+ "FROM gzx_mediator_work mw INNER JOIN gzx_case gc ON mw.case_id = gc.id "
			+ "WHERE mw.create_time BETWEEN #{start} and #{end}  "
			+ "AND mw.liaison_name IS NOT NULL "
			+ "AND gc.company_code = 'WXJK' AND mw.project_code = 'WXJK' " + "LIMIT #{offset}, #{limit}")
	List<GzxMediatorWorkVo> selectHistoryMediaWork(@Param("start") String start, @Param("end") String end,
			@Param("offset") int offset, @Param("limit") int limit);
	
	
	// 获取案件id
	@Select("select count(0) from gzx_case "
			+ "WHERE partner_business_id=#{businessId}")
	Long findCaseId(@Param("businessId") String businessId);
	
	//获取调解记录
	@Select("SELECT count(0) "
			+ "FROM gzx_mediator_work mw INNER JOIN gzx_case gc ON mw.case_id = gc.id "
			+ "WHERE mw.liaison_name IS NOT NULL "
			+ "AND gc.company_code = 'WXJK' "
			+ "AND mw.project_code = 'WXJK' "
			+ "AND gc.partner_business_id=#{businessId}"
			)
	int selectMediaWorkCountByBusinessId(@Param("businessId") String businessId);
	
	//获取调解记录
	@Select("SELECT mw.liaison_name as liaisonName , mw.create_time as createTime,mw.work_type as workType,mw.call_recipient as callRecipient,mw.liaison_phone as liaisonPhone ,mw.work_result as workResult,  gc.partner_business_id as partnerBusinessId,mw.result_code resultCode,mw.remark,mw.promise_repayment_day promiseRepaymentDay,mw.promise_repayment_amount promiseRepaymentAmount  "
			+ "FROM gzx_mediator_work mw INNER JOIN gzx_case gc ON mw.case_id = gc.id "
			+ "WHERE mw.liaison_name IS NOT NULL "
			+ "AND mw.create_time>= #{time} "
			+ "AND gc.company_code = 'WXJK' "
			+ "AND mw.project_code = 'WXJK' "
			+ "AND gc.partner_business_id=#{businessId} "
			+ "order by createTime desc "
			+ "limit 1")
	GzxMediatorWorkVo selectMediaWorkByBusinessId(@Param("time") String time,@Param("businessId") String businessId);
}
